CREATE PROC [dbo].[asi_ProcessFormulaOutput] (
    @inputTempKey uniqueidentifier,
    @inputFormula nvarchar(1000),
    @inputSourceTable nvarchar(200),
    @inputColumnName nvarchar(200),
    @inputColumnValue uniqueidentifier
    )
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @sqlStmt nvarchar(2000)
    DECLARE @substr1 nvarchar(1000)
    DECLARE @substr2 nvarchar(1000)
    DECLARE @substr3 nvarchar(1000)
    DECLARE @substrPreField nvarchar(100)
    DECLARE @substrPostField nvarchar(100)
    DECLARE @substrPreStmt nvarchar(1000)
    DECLARE @substrPostStmt nvarchar(1000)

    DECLARE @pos1 int
    DECLARE @pos2 int
    DECLARE @pos3 int
    DECLARE @pos4 int
    DECLARE @pos5 int

    DECLARE @firstchar nvarchar(1)
    DECLARE @done bit

    IF charindex('[', @inputFormula) > 0
    BEGIN
        -- There are field place-holders to process
        SELECT @sqlStmt = 'select '

        SELECT @substrPreStmt = left (@inputFormula, 1)
        IF @substrPreStmt = '{' or @substrPreStmt = '['
            BEGIN
            SELECT @substrPreStmt = ''
            END
        ELSE
            BEGIN
            SELECT @substrPreStmt = @inputFormula
            SELECT @pos4 = charindex ('[', @substrPreStmt)
            SELECT @pos5 = charindex ('{', @substrPreStmt)
            IF @pos5 < @pos4 and @pos5 <> 0
                 select @pos4 = @pos5
            SELECT @substrPreStmt = left (@substrPreStmt, @pos4 - 1)
            END

        SELECT @sqlStmt = @sqlStmt + '''' + @substrPreStmt + '''' + ' + '

        SELECT @substrPostStmt = left (reverse(@inputFormula), 1)
        IF @substrPostStmt = '}' or @substrPostStmt = ']'
            BEGIN
            SELECT @substrPostStmt = ''
            END
        ELSE
            BEGIN
            SELECT @substrPostStmt = reverse(@inputFormula)
            SELECT @pos4 = charindex (']', @substrPostStmt)
            SELECT @pos5 = charindex ('}', @substrPostStmt)
            IF @pos5 < @pos4 and @pos5 <> 0
                BEGIN
                 SELECT @pos4 = @pos5
                 END
            SELECT @substrPostStmt = left (@substrPostStmt, @pos4 - 1)
            SELECT @substrPostStmt = reverse(@substrPostStmt)
            END

        SELECT @substr1 = substring (@inputFormula, datalength (@substrPreStmt)/2 + 1,
            datalength (@inputFormula)/2 - datalength (@substrPreStmt)/2 - datalength (@substrPostStmt)/2)

        SELECT @done = 0

        WHILE @done = 0
        BEGIN
            SELECT @firstchar = left (@substr1, 1)
            IF @firstchar = '{'
            BEGIN    -- find a matching curly bracket
                SELECT @substr3 = substring (@substr1, 1, charindex ('}', @substr1))
                SELECT @pos2 = datalength (@substr3)/2
                SELECT @substrPreField = substring (@substr1, 2, charindex ('[', @substr1) - 2)
                SELECT @substrPostField = substring (@substr1, charindex (']', @substr1) + 1,
                    (charindex ('}', @substr1) -  charindex (']', @substr1) - 1))
                SELECT @pos3 = (datalength (@substr3)/2) - 4 - (datalength (@substrPreField)/2) - (datalength (@substrPostField)/2)
                SELECT @substr2 = substring (@substr3, charindex ('[', @substr3) + 1, @pos3)
                IF EXISTS (select 1 FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE TABLE_NAME = @inputSourceTable
                        AND COLUMN_NAME = @substr2)
                    SELECT @sqlStmt = @sqlStmt + ' case when ' + @substr2 + ' is null then '''' else ' + '''' + @substrPreField + '''' + ' + ' + @substr2 + ' + ' + '''' + @substrPostField + '''' + ' end '  + ' + '
                ELSE
                    SELECT @sqlStmt = @sqlStmt + '''' + @substr3 + '''' + ' + '

                SELECT @substr1 = right (@substr1, datalength (@substr1)/2 - @pos2)
            END
            ELSE IF @firstchar = '['
            BEGIN    -- find a matching square bracket
                SELECT @substr3 = substring (@substr1, 1, charindex (']', @substr1))
                SELECT @substr2 = substring (@substr3, 2, datalength (@substr3)/2 -2)    

                IF EXISTS (select 1 from INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_NAME = @inputSourceTable
                    AND COLUMN_NAME = @substr2)
                    SELECT @sqlStmt = @sqlStmt + ' case WHEN ' + @substr2 + ' IS NULL THEN '''' ELSE ' + @substr2 + ' end '  + ' + '
                ELSE
                    SELECT @sqlStmt = @sqlStmt + '''' + @substr3 + '''' + ' + '
                SELECT @substr1 = right (@substr1, datalength (@substr1)/2 - datalength (@substr2) /2 - 2)
            END
            ELSE
            BEGIN    -- find everything up to the next curly or square bracket, and treat it as a literal
                SELECT @pos4 = charindex ('[', @substr1)
                SELECT @pos5 = charindex ('{', @substr1)
                IF @pos5 < @pos4 and @pos5 <> 0
                     SELECT @pos4 = @pos5
                SELECT @substr2 = left (@substr1, @pos4)

                IF (datalength (@substr2) / 2) > 0
                    BEGIN
                    SELECT @substr2 = left (@substr2, datalength (@substr2)/2 - 1)
                    SELECT @sqlStmt = @sqlStmt + '''' + @substr2 + '''' + ' + '
                    END
                ELSE
                    SELECT @done = 1
                SELECT @substr1 = right (@substr1, datalength (@substr1)/2 - datalength (@substr2) /2)
            END

        END -- while @done = 0

        IF substring (@sqlStmt, (datalength (@sqlStmt)/2) - 1, 1) = '+'
            SELECT @sqlStmt = substring (@sqlStmt, 1, (datalength (@sqlStmt)/2) - 2)

        IF (datalength (@substrPostStmt)/2 > 0)
             SELECT @sqlStmt = @sqlStmt + ' + ' + '''' + @substrPostStmt + ''''

        -- support <n> for embedded newline, <t> for embedded tab anywhere in formula
        WHILE (charindex ('<n>', @sqlStmt) > 0)
            SELECT @sqlStmt = replace (@sqlStmt, '<n>', ''' + char(13) + char(10) + ''')

        WHILE (charindex ('<t>', @sqlStmt) > 0)
            SELECT @sqlStmt = replace (@sqlStmt, '<t>', ''' + char(9) + ''')

        -- support <[> and <]> to embed square brackets anywhere in formula
        WHILE (charindex ('<[>', @sqlStmt) > 0)
            SELECT @sqlStmt = replace (@sqlStmt, '<[>', '[')

        WHILE (charindex ('<]>', @sqlStmt) > 0)
            SELECT @sqlStmt = replace (@sqlStmt, '<]>', ']')

        -- support <{> and <}> to embed curly brackets anywhere in formula
        WHILE (charindex ('<{>', @sqlStmt) > 0)
            SELECT @sqlStmt = replace (@sqlStmt, '<{>', '{')

        WHILE (charindex ('<}>', @sqlStmt) > 0)
            SELECT @sqlStmt = replace (@sqlStmt, '<}>', '}')

        SELECT @sqlStmt = @sqlStmt + ' from ' + @inputSourceTable + ' where ' + @inputColumnName + ' = '
            + '''' + convert (nvarchar(50), @inputColumnValue) + ''''

        CREATE TABLE #tmptbl (formattedOutput nvarchar(1000))
        INSERT INTO #tmptbl  exec (@sqlStmt)
        INSERT tempFormula (tempKey, tempValue)
            SELECT @inputTempKey, formattedOutput FROM #tmptbl
    END
    ELSE
    BEGIN
        -- There are no field place-holders to process, so just return the formula
        INSERT tempFormula VALUES (@inputTempKey, @inputFormula)
    END

    SET NOCOUNT OFF

END -- create proc ProcessContactFormula

GO